﻿CREATE PROCEDURE [Cucina].[SP_Stats_StatoComande]
	@DataGiornata datetime,
	@PuntoDistribuzioneID int
AS
	SELECT SUBSTRING(Codicecomanda, 1, 1) AS Lettera, COUNT(DISTINCT C.ComandaID) AS Comande, COUNT(DISTINCT POC.ProdottiOrdinatiComandaID) AS Prodotti, COUNT(DISTINCT CCI.ComandaCheckInID) AS Checked, MIN(C.DataOra) AS DataOraMin, MAX(C.DataOra) AS DataOraMax, MIN(CCI.DataOra) DataOraCheckInMin, MAX(CCI.DataOra) AS DataOraCheckInMax, COUNT(DISTINCT CSR.ComandaStampaRemotaID) AS Printed, MIN(CSR.DataOra) AS DataOraStampaMin, MAX(CSR.DataOra) AS DataOraStampaMax, COUNT(DISTINCT CC.ComandaConsegnaID) AS Delivered, MIN(CC.DataOra) AS DataOraConsegnaMin, MAX(CC.DataOra) AS DataOraConsegnaMax
	FROM Cucina.Comanda C
	INNER JOIN Cucina.ProdottiOrdinatiComanda POC
		ON C.ComandaID = POC.ComandaID
	LEFT JOIN Cucina.ComandaCheckIn CCI
		ON C.ComandaID = CCI.ComandaID
	LEFT JOIN Cucina.ComandaStampaRemota CSR
		ON C.ComandaID = CSR.ComandaID
	LEFT JOIN Cucina.ComandaConsegna CC
		ON C.ComandaID = CC.ComandaID
	WHERE (IsCheckInRichiesto = 1 OR IsStampaRemotaRichiesta = 1 OR IsConsegnaRichiesta = 1)
	AND C.DataOra BETWEEN DATEADD(hour, 3, CAST(CAST(@DataGiornata AS Date) AS DateTime)) AND DATEADD(hour, 27, CAST(CAST(@DataGiornata AS Date) AS DateTime))
	AND PuntoDistribuzioneID = @PuntoDistribuzioneID
	AND C.ComandaID NOT IN (SELECT ComandaID FROM Cucina.ComandaDeleted)
	AND C.ComandaID NOT IN (SELECT ComandaID FROM Cucina.ComandaLocked)
	GROUP BY SUBSTRING(Codicecomanda, 1, 1)
	ORDER BY SUBSTRING(CodiceComanda, 1, 1) DESC
RETURN 0